package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;

import java.util.List;

@Mapper
public interface Vomapper {
   @Select("select bp.name,sum(oi.final_total_amount) 'value' from order_info oi\n" +
           "left join base_province bp on oi.province_id = bp.id\n" +
           "group by bp.name")
    List<Vo> getList();
    @Select("select b.name,sum(oi.final_total_amount) 'value' from order_info oi\n" +
            "left join order_detail o on oi.id = o.order_id\n" +
            "left join  sku_info sk on o.sku_id = sk.id\n" +
            "left join base_trademark b on sk.tm_id = b.id\n" +
            "group by b.name\n" +
            "order by value")
    List<Vo> getList2();
    @Select("select b1.name,sum(oi.final_total_amount) 'value' from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info sk on od.sku_id = sk.id\n" +
            "left join  base_category3 b3 on sk.category3_id = b3.id\n" +
            "left join base_category2 b2 on b3.category2_id = b2.id\n" +
            "left join base_category1 b1 on b2.category1_id = b1.id\n" +
            "group by b1.name\n" +
            "order by value")
    List<Vo> getList3();
@Select("select '用户数(人)' name,a.num1,b.num2 from\n" +
        "(select count(*) num1 from user_info where timestampdiff(year,create_time,now())<1) a,\n" +
        "(select count(*) num2 from user_info where timestampdiff(year,create_time,now())>=1) b")
 List<Vo1> getList4();


@Select("select b2.name,sum(final_total_amount) 'value' from  order_info oi\n" +
        "left join order_detail od on oi.id = od.order_id\n" +
        "left join sku_info sk on od.sku_id = sk.id\n" +
        "left join  base_category3 b3 on sk.category3_id = b3.id\n" +
        "left join base_category2 b2 on b3.category2_id = b2.id\n" +
        "group by b2.name\n" +
        "order by value")
List<Vo> getList5();

@Select("select  concat(substr(ui.birthday,3,1),'0后')'name',sum(final_total_amount)'value' from  order_info oi\n" +
        "left join user_info ui on ui.id = oi.user_id\n" +
        "group by concat(substr(ui.birthday,3,1),'0后')")
 List<Vo> getList6();

@Select("select yys name,sum(final_total_amount) value from order_info\n" +
        "where yys is not null\n" +
        "group by yys")
 List<Vo> getList7();

@Select("select bt.name,if(gender='F','男','女') gender,sum(final_total_amount) price from order_info oi\n" +
        "left join user_info ui on oi.user_id = ui.id\n" +
        "left join order_detail od on oi.id = od.order_id\n" +
        "left join sku_info si on od.sku_id = si.id\n" +
        "left join base_trademark bt on si.tm_id = bt.id\n" +
        "group by  bt.name,if(gender='F','男','女')")
 List<Vo2> getList8();

@Select("select bt.name, sum(final_total_amount) value from order_info oi\n" +
        "left join user_info ui on oi.user_id = ui.id\n" +
        "left join order_detail od on oi.id = od.order_id\n" +
        "left join sku_info si on od.sku_id = si.id\n" +
        "left join base_trademark bt on si.tm_id = bt.id\n" +
        "group by bt.name")
 List<Vo> getList9();

 @Select("select sum(final_total_amount) from order_info")
 Double getList10();

}
